package com.yidu.DailyFinalTreatment.mapper;

import com.yidu.DailyFinalTreatment.entity.SecuritiesNetWorth;
import org.apache.catalina.LifecycleState;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository
@Mapper
public interface SecuritiesNetWorthMapper {
    /**
     * 证券查询方法
     * @param dateTime 当日时间
     * @param fundId   基金ID
     * @param dateTimeTwo 当为周六周日时，需要传入的时间条件
     * @return
     */
    @Select("SELECT (s.securityId)securityId,(s.securityName)securityName,\n" +
            "(CASE s.securitType WHEN 1 THEN '债券' WHEN 2 THEN '股票' END) AS securitType,(m.endMoney)endMoney,\n" +
            "(st.securityNum)securityNum,(st.securityMoney)securityMoney,(st.securityNum*m.endMoney)marketValue,(st.securityNum*m.endMoney-st.securityMoney)totalPrice\n" +
            " FROM (SELECT * FROM SECURITY)s\n" +
            "JOIN\n" +
            "(SELECT *FROM market WHERE inputDate=#{dateTime}) m ON s.securityId=m.securityId\n" +
            "JOIN\n" +
            "(SELECT * FROM securityInventors WHERE securityInventorsTime=#{dateTime} AND fundId=#{fundId})st ON st.securityId=s.securityId")
    public List<SecuritiesNetWorth> selectSecuritiesNetWorth(String dateTime, String fundId, String dateTimeTwo);

    /**
     * 债券信息方法
     * @param dateTime 查询日期
     * @param fundId   基金ID
     * @param type     类型
     * @return
     */
    @Select("SELECT securityId,SUM(IFNULL(securityTotalAmount,0))TOTALPRICE  FROM securityinventory \n" +
            "WHERE securityInventoryTime=#{dateTime} AND fundId=#{fundId} AND securityType=#{type} GROUP BY securityId")
    public List<SecuritiesNetWorth> selectDebentureInterestNetWorth(String dateTime, String fundId, int type);

    /**
     *
     * @param dateTime 查询日期
     * @param fundId   基金ID
     * @param type     类型
     * @param flag     流入流出
     * @return
     */
    @Select("SELECT securityId,SUM(IFNULL(securityTotalAmount,0))TOTALPRICE  FROM securityinventory \n" +
            "WHERE securityInventoryTime=#{dateTime} AND fundId=#{fundId} AND securityType=#{type} AND businessStatu=#{flag} GROUP BY securityId")
    public List<SecuritiesNetWorth> selectSecuritiesClearingNetWorth(String dateTime, String fundId, int type, int flag);


}
